# coding=utf-8
"""
author : l00464502
date : 2021-10-10
"""

import datetime
import json

import requests
from bs4 import BeautifulSoup
from db_operate import DbOperate


class Opengaussissuecollect:
    priority_dict = {
        '4': ['严重', 10],
        '3': ['主要', 3],
        '2': ['次要', 1],
        '1': ['不重要', 0.1],
        '0': ['无优先级', 0]
    }
    author_dict = {}
    assign_dict = {}

    def __init__(self, db_name, db_user, db_password, db_host, db_port):
        self.db_info = {'db_name': db_name,
                        'db_user': db_user,
                        'db_password': db_password,
                        'db_host': db_host,
                        'port': db_port}

    @staticmethod
    def transfer_time_format():
        """
        获取当前时间，返回的时间格式
        20181006T173008+80-20181007T173008+08（区间），
        或者 -20181007T173008+80（小于20181007T173008+08），
        或者 20181006T173008+80-（大于20181006T173008+08），
        要求时间格式为20181006T173008+08
        :return: 返回ISO8601的时间格式
        """
        now_time = datetime.datetime.now().strftime("%Y-%m-%dT %H:%M:%S")
        create_time = (
                datetime.datetime.strptime(now_time, "%Y-%m-%dT %H:%M:%S") -
                datetime.timedelta(hours=1)).strftime("%Y%m%dT%H%M%S+08-")
        return create_time

    @staticmethod
    def get_requests(page=1):
        # 代理需单独修改
        proxies = {
            'http': 'http://l00464502:7T-R0017@proxy.huawei.com:8080/',
            'https': 'http://l00464502:7T-R0017@proxy.huawei.com:8080/'}
        # 请求内容
        headers = {
            "Content-type": "application/json"
        }
        params = {
            "access_token": "43f9f48af74f24a87e0d5139a6459a6a",
            "enterprise": "opengaussorg",
            "page": page,
            "per_page": 100,
            "state": "all",
        }
        url = "https://gitee.com/api/v5/enterprises/opengaussorg/issues"
        req = requests.get(url=url, params=params, headers=headers,
                           proxies=proxies, verify=False)
        content = req.content.decode('utf-8')
        soup = BeautifulSoup(content, 'html.parser')
        return soup

    def get_info(self):
        # 任务: 190708
        # 需求: 190709
        # 缺陷: 190710
        # 咨询：363814
        page = 1
        issue_infos = []
        issue_category = {
            "190708": ["任务", 0],
            "190709": ["需求", 0],
            "190710": ["缺陷", 0],
            "363814": ["咨询", 0]
        }
        while True:
            print(page)
            pi = json.loads(self.get_requests(page).text, strict=False)
            print(pi)
            if not pi:
                print(issue_infos, issue_category)
                issue_category_form = []
                for issue_c in issue_category.keys():
                    print(f"('{issue_category[issue_c][0]}',"
                          f"'{issue_c}','{issue_category[issue_c][1]}')")
                    issue_category_form.append(
                        f"('{issue_category[issue_c][0]}',"
                        f"'{issue_c}','{issue_category[issue_c][1]}')")
                return [issue_infos, issue_category_form]
            else:
                for issue_info in pi:
                    tmp = ""
                    user_id = issue_info["user"]["login"]
                    assigee_id = issue_info["assignee"]["login"] \
                        if issue_info["assignee"] else ""
                    # 统计issue的分布
                    id_list = [190708, 190709, 363814]
                    if issue_info["issue_type_detail"]["id"] in id_list:
                        issue_category[str(
                            issue_info["issue_type_detail"]["id"])][1] += 1
                    else:
                        issue_category[str(
                            issue_info["issue_type_detail"]["id"])][1] += 1
                    # 统计bug信息
                    if issue_info["issue_type_detail"]["id"] == 190710:
                        print(issue_info["repository"])
                        project = issue_info["repository"]["full_name"] \
                            if issue_info["repository"] else ""
                        issue = issue_info["number"]
                        title = issue_info["title"]
                        # \转义字符存在title中，需要处理，才能正确插入
                        if "\\" in title:
                            title = title.replace("\\", "\\\\")
                        # '存在title中，需要转义，才能正确插入
                        if "'" in title:
                            title = title.replace("'", "\\'")
                        priority = self.priority_dict[str(
                            issue_info["priority"])][0]
                        di = self.priority_dict[str(
                            issue_info["priority"])][1]
                        creator = self.author_dict[user_id] \
                            if user_id in self.author_dict.keys() \
                            else issue_info["user"]["name"]
                        if issue_info["assignee"]:
                            assignee = self.assign_dict[assigee_id] \
                                if assigee_id in self.assign_dict.keys() \
                                else issue_info["assignee"]["name"]
                        else:
                            assignee = ""
                        link = issue_info["html_url"]
                        # 时间格式转化
                        # 如将2021-09-29T14:23:00+08:00格式转化为2021-09-29 14:23:00
                        time = issue_info["created_at"].replace("T", " ")[:-6]
                        time = datetime.datetime.strptime(time,
                                                          '%Y-%m-%d %H:%M:%S')
                        state = issue_info["issue_state"]
                        tmp += f"('{issue}', '{project}', E'{title}', " \
                            f"'{priority}', '{di}', '{creator}', " \
                            f"'{assignee}', '{link}', '{state}', '{time}')"
                    # 将每条数据归属到对应的仓库中
                    if tmp and tmp not in issue_infos:
                        issue_infos.append(tmp)
            page += 1

    def push_data_to_db(self):
        data = self.get_info()
        if not data:
            print("注意，数据抓取失败，请分析")
            return

        # 数据库连接信息
        my_db = DbOperate(**self.db_info)
        my_db.conn_db()

        # 表信息
        table_list = ["ReportApp_issuesinfo", "ReportApp_issuescategory"]

        # 先清理表中数据
        for table in table_list:
            del_sql_cmd = "begin;\n"
            del_sql_cmd += f'delete from "{table}";\n'
            del_sql_cmd += "end;"
            my_db.exec_dml_sql(del_sql_cmd)

        # 给table_list中各表插入最新数据
        for i in range(len(table_list)):
            insert_sql_cmd = "begin;\n"
            if i == 0:
                insert_sql_cmd += f'insert into "{table_list[i]}"(' \
                    f'issue_id, category, title, priority, di, creater, ' \
                    f'assignee, link, state, create_date) values '
            else:
                insert_sql_cmd += f'insert into "{table_list[i]}"' \
                    f'(issue_category, issue_category_id, ' \
                    f'issue_count) values '
            insert_sql_cmd += ',\n'.join(data[i])
            insert_sql_cmd += ";\nend;"
            my_db.exec_dml_sql(insert_sql_cmd)
            print(f"{table_list[i]}表数据插入完成")

        # 关闭数据库连接
        my_db.close_conn()


if __name__ == '__main__':
    solution = Opengaussissuecollect("db_name", "db_user", "db_pwd", "IP",
                                     "port")
    solution.push_data_to_db()
